mp4_trial

female <- dbGetQuery (db,
                    'select count(ci.movie_id) as total, info as genre
from cast_info ci
join movie_info m on m.movie_id = ci.movie_id ##join to filter by person_role_id
join name n on n.id = ci.person_id ##join to filter by gender
join role_type r on r.id = ci.role_id
join title t on t.id = ci.movie_id
where r.id = 3
and n.gender = "f"
and m.info_type_id = 3
and t.production_year > 2009
group by m.info
order by total desc
limit 0,5;')

ggplot(female, aes(x = genre, y = total/1000, fill = genre)) +
  geom_bar(stat = "identity") +
  scale_y_continuous(limits = c(0,80),expand = c(0,0), breaks = c(0,20,40,60,80)) +
  scale_fill_brewer("Genre",palette = "Set2")+
  theme(axis.text.x = element_text(size = 12),
        axis.title.x = element_text(family = "Century Gothic", 
                                    color = "black", face = "bold", size = 13),
        axis.text.y = element_text(family = "Century Gothic", 
                                    color = "black", size = 12), 
        axis.title.y = element_text(family = "Century Gothic", 
                                    color = "black", face = "bold", size = 13),
        plot.title = element_text(family = "Century Gothic", 
                                    color = "black", face = "bold", size = 20),
        legend.text = element_text(family = "Century Gothic",size = 12),
        legend.title = element_text(family = "Century Gothic", face = "bold", size = 14))+
   labs(title = "Top Five Genres Among Women Producers", x = "Genre", y = "Number of Movies(in 1000s)")

<<<<<<< HEAD ======= >>>>>>> fe8224e239695e40e2367d2200ffef0ef1e5981a

Top 5 genres

test <- dbGetQuery(db, 'SELECT m.info, t.production_year, ci.movie_id, n.gender
FROM imdb.cast_info ci
inner join name n on n.id = ci.person_id
inner join title t on t.id = ci.movie_id 
inner join role_type r on r.id = ci.role_id
inner join movie_info m on m.movie_id = ci.movie_id
where m.info_type_id =3
and (n.gender is not NULL)
and r.id = 3
and t.production_year >2009
and t.production_year <2016
and m.info in (\"Comedy\", \"Drama\", \"Documentary\", \"Short\" , \"Thriller\");')

test <- test %>%
  group_by(production_year, info, gender)%>%
  summarise(total =n())
test <- test %>%
  mutate(Gender =  recode_factor(gender, 
                                 "f" = "Female",
                                 "m" = "Male"))%>%
  select(info, Gender, production_year, total)
test

fill_c <- c("blue", "red")
ggplot(test, aes(x = production_year, y = total/1000))+
  geom_line(aes(color = Gender), size = 5)+
  scale_y_continuous(limits = c(0,40), expand = c(0,0), breaks = c(0,10,20,30,40)) +
  scale_x_discrete(limits = c(2010, 2011, 2012, 2013, 2014, 2015), expand = c(0,0)) +
  facet_wrap(~info)+
  theme_bw()+
  scale_color_manual("Gender",labels = c("Female", "Male"), values = fill_c)+
<<<<<<< HEAD
  theme(axis.text.x = element_text(angle = 90, hjust = 0.4, size = 48), 
=======
    theme(axis.text.x = element_text(angle = 90, hjust = 0.4, size = 48), 
>>>>>>> fe8224e239695e40e2367d2200ffef0ef1e5981a
        axis.title.x = element_text(family = "Century Gothic", 
                                    color = "black", face = "bold", size = 48),
        axis.text.y = element_text(family = "Century Gothic", 
                                    color = "black", size = 48), 
        axis.title.y = element_text(family = "Century Gothic", 
                                    color = "black", face = "bold", size = 48),
        plot.title = element_text(family = "Century Gothic", 
                                    color = "black", face = "bold", size = 68),
        strip.text = element_text(family = "Century Gothic", face = "bold", size = 48),
        legend.text = element_text(family = "Century Gothic", size = 48),
        legend.title = element_text(family = "Century Gothic", face = "bold", size =52),
        legend.position = "bottom",
        panel.spacing = unit(4, "lines"))+
  labs(title = "Number of Movies Produced by Men/Women in the Top Five Genres", x = "Year", y = "Number of Movies(in 1000s)")

<<<<<<< HEAD

======= <<<<<<< HEAD

=======

>>>>>>> 4b51fe5ae6aa66bece3a3891a69bf6de908a1781 >>>>>>> fe8224e239695e40e2367d2200ffef0ef1e5981a